package com.saic.localfileloader.main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

/**
 * @DESC:ORACLE操作
 * @author Xinshiyou
 */
public class DBUtil {

	// Oracle Produce
	public static final String oracleUrl = "jdbc:oracle:thin:@10.129.11.129:1521:ESSBASE";
	public static final String oracleName = "oracle.jdbc.driver.OracleDriver";

	// MySQL Test
	public static final String mysqlUrl = "jdbc:mysql://localhost:3306/test";
	public static final String mysqlName = "com.mysql.jdbc.Driver";

	// Set for Use
	public static final String url = oracleUrl;
	public static final String name = oracleName;

	public static final String user = "SAIC_ETL";
	public static final String password = "SAICetl1";

	public Connection conn = null;

	public DBUtil() {
		System.out.println("URL: " + url);
		init();
	}

	/** create table */
	public boolean createTable(String tableName, List<String> colName) {
		pm("SqlDBUtil", "SQL table: " + tableName);
		StringBuffer sb = new StringBuffer();
		sb.append("create table " + tableName + "(");
		for (String s : colName)
			sb.append("" + s + " varchar(80),");
		String sql = sb.substring(0, sb.length() - 1);
		sql += ");";
		return execute(sql);
	}

	/** Create, Insert, Delete, Drop */
	public boolean execute(String sql) {

		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			return ps.execute();
		} catch (SQLException e) {
			pm("SqlDBUtil-execute", e.getMessage());
		} finally {
			if (null != ps)
				try {
					ps.close();
				} catch (SQLException e) {
					pm("SqlDBUtil-execute", e.getMessage());
				}
		}
		return false;
	}

	public boolean insert(String sql) {
		try {
			Statement state = conn.createStatement();
			state.execute(sql);
			state.close();
		} catch (SQLException e) {
			System.out.println(sql);
			System.out.println(e.getMessage());
		}
		return false;
	}

	/** Create, Insert, Delete, Drop */
	public ResultSet executeQuery(String sql) {

		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			return ps.executeQuery();
		} catch (SQLException e) {
			pm("SqlDBUtil-execute", e.getMessage());
		} finally {
		}
		return null;
	}

	private static void pm(String cls, String msg) {
		System.out.println(cls + "\t" + msg);
	}

	/** Create, Insert, Delete, Drop */
	public boolean update(String sql) {

		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			return ps.executeUpdate() > 0;
		} catch (SQLException e) {
		} finally {
			if (null != ps)
				try {
					ps.close();
				} catch (SQLException e) {
				}
		}
		return false;
	}

	public int queryCount(String sql) {
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			int resSum = 0;
			while (rs.next())
				resSum++;
			return resSum;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (null != ps)
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		return 0;
	}

	/** Initialize */
	public void init() {
		try {
			Class.forName(name);
			conn = DriverManager.getConnection(url, user, password);
			if (null == conn)
				System.out.println("Connect is NULL");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/** Close database */
	public void close() {
		try {
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
